# Copyright (c) 2005,2006,2007 Dynamic Solutions Inc. (support@dynamic-solutions.com)
#
# This file is part of DataZoomer.
#
# DataZoomer is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 3 of the License, or
# (at your option) any later version.
#
# DataZoomer is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.

"""Provides a high level interface to map database tables
to data model objects.
"""

from types import DictType
from dzentity import Entity
from zoom import zoomer
import datetime

db = zoomer.db

table_prefix = 'sam_'

class MissingRecordError(Exception): pass


class Record(Entity):
    def __init__(self,rec=None,table=None):
        self.table = table
        Entity.__init__(self,rec)
        if not rec:
            self.clear()

    def save(self):
        self.table.update(self)

    def saveas(self,id):
        self.__dict__[self.table.table_key] = id
        self.table.insert(self)

    def delete(self):
        self.table.delete(self)

    def clear(self):
        pass

    def update(self,keywords):
        for key in self.keys():
            if key in keywords:
                self.__dict__[key] = keywords[key]

class RecordIterator:
    def __init__(self,rs,entity_class):
        self.entity_class = entity_class
        self.rs = rs

    def __iter__(self):
        for rec in self.rs:
            obj = self.entity_class(rec)
            yield obj

    def __len__(self):
        return len(self.rs)

class Table:
    labels = {}

    def __init__(self,entity_class):
        self.entity_class = entity_class
        self.table_key = 'ID'
        self.list_order = None

    # Calculated Fields
    #--------------------------------------------------------------------------
    def get_name(self):
        return self.__class__.__name__

    def get_data_type(self):
        return self.name.lower()

    def get_table_name(self):
        return (self.table_prefix + self.name).lower()

    def get_table_prefix(self):
        return table_prefix

    def get_columns(self):
        self.columns = db('select * from %s where 1=0' % (self.table_name)).columns
        return self.columns

#    def get_field_names(self):
#        self.field_names = self.columns.names()

    def get_field_names(self):
        self.field_names = db('select * from %s where 1=0' % (self.table_name)).columns.names()
        return self.field_names

    # Methods
    #--------------------------------------------------------------------------
    def create(self):
        self.create_table()

    def drop(self):
        cmd = 'drop table if exists %(table_name)s' % self
        zoomer.db(cmd)

    def new(self,**kwargs):
        """Return a new table record object"""
        if 'key' in kwargs.keys():
            kwargs[self.table_key.upper()] = kwargs['key']
            del kwargs['key']
        return self.entity_class(kwargs)

    def insert(self,rec):
        """Insert a table record"""
        raw_values = rec.field_values(self.field_names)
        field_names = [key for key in self.field_names if not key==self.table_key and raw_values[key]!=None]

#         if 'DTADD' in field_names:
#             rec.DTADD = datetime.datetime.now()
#         if 'DTUPD' in field_names:
#             rec.DTUPD = datetime.datetime.now()
# 
        field_values = rec.field_values(field_names)

        fields = ','.join(field_names)
        params = ','.join(['%s'] * len(field_names))
        values = [field_values[key] for key in field_names]

        cmd = 'insert into %s (%s) values (%s)' % (self.table_name,fields,params)
        db(cmd,*values)
        rec.ID = db.lastrowid
        return rec.ID

    def update(self,rec):
        """Update a table record"""
        field_names  = self.field_names
        if 'DTUPD' in field_names:
            rec.DTUPD = datetime.datetime.now()
        field_values = rec.field_values(field_names)

        # only update non-key fields that have values
        fields_to_update = [key for key in field_names if not key==self.table_key and field_values[key]!=None]

        params = ','.join(['%s=%s' % (key,'%s') for key in fields_to_update])
        values = [field_values[key] for key in fields_to_update]

        cmd = 'update %s set %s where %s=%s' % (self.table_name,params,self.table_key,field_values[self.table_key])
        db(cmd,*values)

    def delete(self,rec):
        """Delete a table record"""
        if not rec:
            raise MissingRecordError('attempt to delete unknown record')

        # if rec is an Entity, find the id field - otherwise assume rec is the id
        #  allow you to delete a record knowing the key but not having to get a full record
        #_id = isinstance(rec,Entity) and rec.__dict__[self.table_key] or rec
        _id = isinstance(rec,Entity) and getattr(rec,self.table_key) or rec
        cmd = 'delete from %s where %s=%s' % (self.table_name,self.table_key,'%s')
        db(cmd,_id)

    def get(self,key):
        """Return an individual record"""
        cmd = 'select * from %s where %s=%s' % (self.table_name,self.table_key,'%s')
        rs = zoomer.db(cmd,key)
        return len(rs)==1 and rs[0] or zoomer.db('select * from %s where 1=2'%self.table_name)

    def get_list(self):
        """Return a list of the records"""
        cmd = 'select * from %s%s' % (self.table_name,(self.list_order and ' order by %s'%self.list_order or ''))
        rs = zoomer.db(cmd)
        if len(rs)>0: return RecordIterator(rs,self.entity_class)
        else: return zoomer.db('select * from %s where 1=2'%self.table_name)

    def seek(self,id):
        cmd = 'select * from %s where %s=%s' % (self.table_name,self.table_key,'%s')
        rs = db(cmd,id)
        if len(rs)>0:
            return self.entity_class(rs[0],self)
        else:
            return None

    def locate(self,**keywords):
        where_clause_fields = []
        where_clause_values = []
        for key in keywords:
            where_clause_fields.append(key)
            where_clause_values.append(keywords[key])

        cmd = 'select * from %s where %s' % (self.table_name,' AND '.join(['%s=%s' % (field,'%s') for field in where_clause_fields]))
        rs = db(cmd,*where_clause_values)
        if len(rs)>0:
#            return [obj for obj in RecordIterator(rs,self.entity_class)]
            return RecordIterator(rs,self.entity_class)
        else:
            return []

    def fetch(self):
        rs = db('select * from %s'%self.table_name)
        if len(rs)>0:
            return RecordIterator(rs,self.entity_class)
        else:
            return None

    def find(self,id=None):
        if id:
            cmd = 'select item.* from %s item, dz_owners owners where item.id=%s and item.id=owners.itemid and owners.itemtype=%s and owners.userid=%s' % (self.table_name,'%s','%s','%s')
            ds = db(cmd,id,self.type,zoomer.session.userid)
            if len(ds)==1:
                return ds[0]
            elif len(ds)>1:
                raise 'Expected one record but got %s in query %s' % (len(ds),cmd)
            else:
                return 0
        else:
            cmd = 'select item.* from %s item, dz_owners owners where item.id=owners.itemid and owners.itemtype=%s and owners.userid=%s' % (self.table_name,'%s','%s')
            return db(cmd,self.type,zoomer.session.userid)

    def __getattr__(self,name):
        try:
            method_name = 'get_'+name
            my_method = getattr(self.__class__,method_name)
        except:
            raise AttributeError,"%s instance has no attribute '%s' and no method '%s'" % (self.__class__.__name__,name,method_name)
        return my_method(self)

    def __getitem__(self,key):
        """Provide access to attributes by name"""
        if key in self.__dict__:
            return self.__dict__[key]
        else:
            return self.__getattr__(key)
            
    def __repr__(self):
        return str(db('select * from %s'%self.table_name))

#---------------------
class ManagedTable(Table):
    pass


if __name__ == '__main__':
    import unittest

    class CustomEntity(Entity):
        name = 'Joe'
        def __init__(self,rec=None):
            Entity.__init__(self,rec)
            self.address   = '2345 Smith St'

        def get_age(self):
            return 40

#        def get_phone(self):
#            return '(250) 123-3456'

    class CustomModel(Table):
        def create(self):
            cmd = """
                  create table if not exists %(table_name)s (
                      id          int not null auto_increment,
                      first_name  varchar(30) not null,
                      last_name   varchar(30) not null,
                      email       varchar(80),
                      phone       varchar(20),
                      fax         varchar(20),
                      skype       varchar(40),
                      userid      int,
                      notes       text,
                      created     timestamp,
                      updated     timestamp,
                      PRIMARY KEY (id)
                  ) TYPE=MyISAM;
                  """ % self
            zoomer.db(cmd)

    class ModelTest(unittest.TestCase):
        def test_init(self):
            model = CustomModel(CustomEntity)

        def test_data_type(self):
            model = CustomModel(CustomEntity)
            self.assertEqual(model.data_type,'custommodel')

        def test_table_name(self):
            model = CustomModel(CustomEntity)
            self.assertEqual(model.table_name,'sam_custommodel')

        def test_table_table_prefix(self):
            model = CustomModel(CustomEntity)
            self.assertEqual(model.table_prefix,'sam_')

        def test_create(self):
            contacts = CustomModel(CustomEntity)
            contacts.create()

        def test_drop(self):
            contacts = CustomModel(CustomEntity)
            contacts.drop()

        def test_insert(self):
            contacts = CustomModel(CustomEntity)
            contacts.create()
            new_rec = contacts.new()
            new_rec.ID = 1
            new_rec.FIRST_NAME = 'Joe'
            new_rec.LAST_NAME  = 'Smith'
            new_rec.SKYPE      = 'joe_smith_on_skype'
            new_id = contacts.insert(new_rec)
            self.assert_(None!=new_rec)
            self.assert_(None!=new_id)

        def test_insert_ignore_id(self):
            contacts = CustomModel(CustomEntity)
            contacts.create()
            new_rec = contacts.new()
            new_rec.ID = 1
            new_rec.FIRST_NAME = 'Joe'
            new_rec.LAST_NAME  = 'Smith'
            new_rec.SKYPE      = 'joe_smith_on_skype'
            new_id = contacts.insert(new_rec)
            self.assert_(None!=new_rec)
            self.assert_(None!=new_id)
            new_rec.FIRST_NAME = 'Sally'
            # Insert second person with same ID
            new_id = contacts.insert(new_rec)
            self.assert_(None!=new_rec)
            self.assert_(None!=new_id)

        def test_delete(self):
            contacts = CustomModel(CustomEntity)
            contacts.create()
            rec = contacts.new()
            rec.FIRST_NAME = 'Issac'
            rec.LAST_NAME  = 'Newton'
            new_id = contacts.insert(rec)
            rec = contacts.seek(new_id)
            self.assert_(rec)
            self.assertEqual(rec.ID,new_id)
            contacts.delete(rec)
            rec = contacts.seek(new_id)
            self.assertEqual(rec,None)

        def test_update(self):
            contacts = CustomModel(CustomEntity)
            contacts.create()

            rec = contacts.new()
            rec.FIRST_NAME = 'Issac'
            rec.LAST_NAME  = 'Newtons'
            new_id = contacts.insert(rec)

            rec2 = contacts.seek(new_id)
            self.assertEquals(rec2.FIRST_NAME,'Issac')

            rec2.FIRST_NAME = 'Albert'
            contacts.update(rec2)

            rec2 = contacts.seek(new_id)
            self.assertEquals(rec2.FIRST_NAME,'Albert')

        def test_locate(self):
            contacts = CustomModel(CustomEntity)
            contacts.drop()
            contacts.create()

            rec = contacts.new()
            rec.FIRST_NAME = 'Issac'
            rec.LAST_NAME  = 'Newtons'
            contacts.insert(rec)

            rec = contacts.new()
            rec.FIRST_NAME = 'Johann'
            rec.LAST_NAME  = 'Strauss'
            contacts.insert(rec)

            rec = contacts.new()
            rec.FIRST_NAME = 'Albert'
            rec.LAST_NAME  = 'Falls'
            contacts.insert(rec)

            recs = contacts.locate(FIRST_NAME='Albert',LAST_NAME='Falls')
            self.assert_(recs)
            rec_list = [rec for rec in recs]
            self.assertEquals(rec_list[0].FIRST_NAME,'Albert')
            self.assertEquals(rec_list[0].ID,3)

        def test_insert_and_update(self):
            contacts = CustomModel(CustomEntity)
            contacts.create()

            rec = contacts.new()
            rec.FIRST_NAME = 'Issac'
            rec.LAST_NAME  = 'Newton'
            new_id = contacts.insert(rec)

            rec.FIRST_NAME = 'Albert'
            contacts.update(rec)

            rec2 = contacts.seek(new_id)
            self.assertEquals(rec2.FIRST_NAME,'Albert')

        def test_seek(self):
            contacts = CustomModel(CustomEntity)
            contacts.drop()
            contacts.create()

            rec = contacts.new()
            rec.FIRST_NAME = 'Issac'
            rec.LAST_NAME  = 'Newtons'
            contacts.insert(rec)

            rec = contacts.new()
            rec.FIRST_NAME = 'Johann'
            rec.LAST_NAME  = 'Strauss'
            contacts.insert(rec)

            rec = contacts.new()
            rec.FIRST_NAME = 'Albert'
            rec.LAST_NAME  = 'Falls'
            contacts.insert(rec)

            srec = contacts.seek(3)
            self.assert_(srec)
            self.assertEqual(srec.FIRST_NAME,'Albert')

        def assertRaisesEx(self, exception, callable, *args, **kwargs):
            import sys
            if "exc_args" in kwargs:
                exc_args = kwargs["exc_args"]
                del kwargs["exc_args"]
            else:
                exc_args = None
            if "exc_pattern" in kwargs:
                exc_pattern = kwargs["exc_pattern"]
                del kwargs["exc_pattern"]
            else:
                exc_pattern = None

            argv = [repr(a) for a in args]\
                   + ["%s=%r" % (k,v)  for k,v in kwargs.items()]
            callsig = "%s(%s)" % (callable.__name__, ", ".join(argv))

            try:
                callable(*args, **kwargs)
            except exception, exc:
                if exc_args is not None:
                    self.failIf(exc.args != exc_args,
                                "%s raised %s with unexpected args: "\
                                "expected=%r, actual=%r"\
                                % (callsig, exc.__class__, exc_args, exc.args))
                if exc_pattern is not None:
                    self.failUnless(exc_pattern.search(str(exc)),
                                    "%s raised %s, but the exception "\
                                    "does not match '%s': %r"\
                                    % (callsig, exc.__class__, exc_pattern.pattern,
                                       str(exc)))
            except:
                exc_info = sys.exc_info()
                print exc_info
                self.fail("%s raised an unexpected exception type: "\
                          "expected=%s, actual=%s"\
                          % (callsig, exception, exc_info[0]))
            else:
                self.fail("%s did not raise %s" % (callsig, exception))


    class EntityTest(unittest.TestCase):

        def test_init(self):
            c = CustomEntity()
            self.assertEqual(c.name,'Joe')

        def test_attribute(self):
            c = CustomEntity()
            self.assertEqual(c.name,'Joe')

        def test_attribute_missing(self):
            c = CustomEntity()
            self.assertEqual(c.lastname,None)

        def test_class_attribute(self):
            c = CustomEntity()
            self.assertEqual(c.address,'2345 Smith St')

        def test_item(self):
            c = CustomEntity()
            self.assertEqual(c['name'],'Joe')
            self.assertEqual(c['address'],'2345 Smith St')

        def test_get(self):
            c = CustomEntity()
            self.assertEqual(c.age,40)

        def test_rec_keys(self):
            c = CustomEntity({'lastname':'Jones'})
            self.assertEqual(c.rec_keys(),['lastname'])

        def test_keys(self):
            c = CustomEntity()
            self.assertEqual(c.keys(),['address','age','name'])

        def test_keys_additional(self):
            c = CustomEntity({'lastname':'Jones'})
            self.assertEqual(c.keys(),['address','age','lastname','name'])

        def test_format(self):
            c = CustomEntity()
            self.assertEqual('%(name)s' % c,'Joe')

        def test_len(self):
            c = CustomEntity()
            self.assertEqual(len(c),3)

        def test_iter(self):
            c = CustomEntity()
            vals = [item[1] for item in c]
            self.assertEqual(vals,['2345 Smith St',40,'Joe'])
            vals = [item[0] for item in c]
            self.assertEqual(vals,['address','age','name'])

    unittest.main()
